package com.seabook.store.auth.dao;

import java.sql.ResultSet;

import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.seabook.store.auth.domain.Address;
import com.seabook.store.auth.domain.User;

public class UserDaoImpl extends JdbcDaoSupport implements UserDao {

    private final String SQL_INSERT_ADDRESS = "insert into address (userId, firstname, lastname,phone1,phone2,address1,address2,city,state,country ,postcode,surburb,defaultR,comment, email) values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?)";

    private final String SQL_QUERY_ADDRESS = "select * from address where userId = ? ";

    private final String SQL_UPDATE_ADDRESS = "update address set firstname = ?, lastname=?,  phone1 = ?,phone2 = ?, "
            + "address1 = ?,address2 = ?, city=?, state = ?, country=?, postcode = ?, surburb = ? , email= ?,  defaultR = ? where id = ? ";

    private final String SQL_ADDRESS_ID = "select max(id) from address ";

    private final String SQL_CHANGE_PSW = "update user set password = password(?) where userId = ?";

    private UserRowMapper mapper = new UserRowMapper();

    private AddressRowMapper addressMapper = new AddressRowMapper();

    public synchronized Address addAddress(Address address) {
        if (address.getId() > 0) {
            this.updateAddress(address);
        }
        // TODO Auto-generated method stub
        else {
            this.getJdbcTemplate().update(
                    SQL_INSERT_ADDRESS,
                    new Object[] { address.getUserId(), address.getFirstName(),
                            address.getLastName(), address.getPhone1(),
                            address.getPhone2(), address.getAddress1(),
                            address.getAddress2(), address.getCity(),
                            address.getState(), address.getCountry(),
                            address.getPostcode(), address.getSurburb(),
                            address.isDefaultR(), null,address.getEmail() });
            address.setId(this.getJdbcTemplate().queryForInt(SQL_ADDRESS_ID));
        }
        return address;
    }

    public User checkUser(String userName, String password) {
        if (1 == this
                .getJdbcTemplate()
                .queryForInt(
                        "select count(1) from user where username = ? and password = password(?)",
                        new Object[] { userName, password })) {
            User user = this
                    .getJdbcTemplate()
                    .queryForObject(
                            "select * from user where username = ? and password = password(?)",
                            new Object[] { userName, password }, mapper);
            return user;
        } else {
            return null;
        }
    }

    public User getUserById(int userId) {
        try {
            return this
                    .getJdbcTemplate()
                    .queryForObject(
                            "select user_detail.*, userType from user_detail, user where user.id = user_detail.userId and  user.id = ?",
                            new Object[] { userId }, mapper);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public User getUserByName(String userName) {
        return this.getJdbcTemplate().queryForObject(
                "select * from user_detail where userName = ?",
                new Object[] { userName }, mapper);
    }

    /**
     * create a customer
     */
    public int createUser(String username, String password) {
        this
                .getJdbcTemplate()
                .update(
                        " insert into user (username, password, userType) values (?, password(?), 'CUSTOMER') ",
                        new Object[] { username.trim(), password.trim() });

        int userId = this.getJdbcTemplate().queryForInt(
                " select id from user where username = ? ",
                new Object[] { username });
        this.getJdbcTemplate().update(
                " insert into user_detail (username, userId) values (?,?) ",
                new Object[] { username.trim(), userId });
        return userId;
    }

    public int saveUser(User user) {
        int userId = this.getJdbcTemplate().queryForInt(
                "select id from user_detail where userId = ?",
                new Object[] { user.getUserId() });
        if (userId > 0) {
            this
                    .getJdbcTemplate()
                    .update(
                            "update user_detail set email=?, phone=? where userId = ? ",
                            new Object[] { user.getEmail(), user.getPhone(),
                                    user.getUserId() });
        } else {
            this
                    .getJdbcTemplate()
                    .update(
                            "insert into user_detail userId, phone, email) values (?,?,?)",
                            new Object[] { user.getUserId(), user.getPhone(),
                                    user.getEmail() });
            userId = this.getJdbcTemplate().queryForInt(
                    "select id from user_detail where userId = ?",
                    new Object[] { user.getUserId() });
        }
        return userId;
    }

    public boolean validateUserName(String userName) {
        return 0 == this.getJdbcTemplate().queryForInt(
                " select count(1) from user where username = ? ",
                new Object[] { userName });
    }

    class UserRowMapper implements RowMapper<User> {
        public User mapRow(ResultSet rs, int rowNumber) throws SQLException {
            String userType = rs.getString("userType");
            User user;
            if (userType.equals(User.TYPE_ADMIN)) {
                user = new User();
                user.setUserName(rs.getString("username"));
                user.setUserId(rs.getInt("userId"));
                user.setAdmin(true);
            } else {
                user = new User();
                user.setUserName(rs.getString("username"));
                if (rs.getMetaData().getColumnCount() > 4) {
                    user.setUserId(rs.getInt("userId"));
                    user.setEmail(rs.getString("email"));
                    user.setPhone(rs.getString("phone"));
                    user.setUserId(rs.getInt("userId"));
                } else {
                    user.setUserId(rs.getInt("id"));
                }
            }
            return user;
        }
    }

    class AddressRowMapper implements RowMapper<Address> {
        public Address mapRow(ResultSet rs, int rowNumber) throws SQLException {
            Address address = new Address();
            address.setId(rs.getInt("id"));
            address.setUserId(rs.getInt("userId"));
            address.setFirstName(rs.getString("firstname"));
            address.setLastName(rs.getString("lastname"));
            address.setAddress1(rs.getString("address1"));
            address.setAddress2(rs.getString("address2"));
            address.setCity(rs.getString("city"));
            address.setCountry(rs.getString("country"));
            address.setDefaultR(rs.getBoolean("defaultR"));
            address.setPhone1(rs.getString("phone1"));
            address.setPhone2(rs.getString("phone2"));
            address.setPostcode(rs.getString("postcode"));
            address.setState(rs.getString("state"));
            address.setSurburb(rs.getString("surburb"));
            return address;
        }
    }

    public User getUserDetail(User user) {
        return this.getUserById(user.getUserId());
    }

    public List<Address> getAddress(int userId) {
        return this.getJdbcTemplate().query(SQL_QUERY_ADDRESS,
                new Object[] { userId }, addressMapper);
    }

    public boolean removeAddress(Address address) {
        // TODO Auto-generated method stub
        return false;
    }

    public void updateAddress(Address address) {
        this.getJdbcTemplate().update(
                SQL_UPDATE_ADDRESS,
                new Object[] { address.getFirstName(), address.getLastName(),
                        address.getPhone1(), address.getPhone2(),
                        address.getAddress1(), address.getAddress2(),
                        address.getCity(), address.getState(),
                        address.getCountry(), address.getPostcode(),
                        address.getSurburb(), address.getEmail(),address.isDefaultR(),
                        address.getId() });

    }

    public void changePassword(int userId, String password) {
        this.getJdbcTemplate().update(SQL_CHANGE_PSW,
                new Object[] { password, userId });
    }

}
